package com.apobates.forum.letterbox.impl.dao;

import com.apobates.forum.letterbox.dao.InboxDao;
import com.apobates.forum.letterbox.entity.ForumLetter;
import com.apobates.forum.letterbox.entity.ForumLetterTypeEnum;
import com.apobates.forum.letterbox.entity.Inbox;
import com.apobates.forum.utils.persistence.Page;
import com.apobates.forum.utils.persistence.Pageable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Stream;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author xiaofanku
 * @since 20200511
 */
@Repository
public class InboxDaoImpl implements InboxDao{
    @PersistenceContext
    private EntityManager entityManager;
    @Value("${jpa.batch.size}")
    private int batchSize;
    private final static Logger logger = LoggerFactory.getLogger(InboxDaoImpl.class);
    /**
     * [NativeSQL]
     * @param memberId
     * @param pageable
     * @return 
     */
    @Override
    public Page<ForumLetter> findAllByReceiver(long memberId, Pageable pageable) {
        final long count = countByReceiver(memberId);
        if (count == 0) {
            return emptyResult();
        }
        String SQL = "SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.USABLE = ?2 ORDER BY ib.ID DESC";
        Query query = entityManager.createNativeQuery(SQL, ForumLetter.class).setParameter(1, memberId).setParameter(2, true);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        @SuppressWarnings("unchecked")
        final List<ForumLetter> result = query.getResultList();
        return new Page<ForumLetter>() {
            @Override
            public long getTotalElements() {
                return count;
            }
            
            @Override
            public Stream<ForumLetter> getResult() {
                return result.stream();
            }
        };
    }
    private long countByReceiver(long memberId) {
        try {
            return entityManager.createQuery("SELECT COUNT(ib) FROM Inbox ib WHERE ib.member = ?1 AND ib.usable = ?2", Long.class)
                    .setParameter(1, memberId)
                    .setParameter(2, true)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[countByReceiver][InboxDao]", e);
            }
        }
        return 0L;
    }
    /**
     * [NativeSQL]
     * @param memberId
     * @param pageable
     * @return 
     */
    @Override
    public Page<ForumLetter> findAllByReceiverGroupSender(long memberId, Pageable pageable) {
        final long count = countByReceiverGroupSender(memberId);
        if (count == 0) {
            return emptyResult();
        }
        String SQL = "SELECT t.* FROM (SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 ORDER BY ib.ID DESC)AS t GROUP BY t.AUTHOR ORDER BY t.ID DESC";
        Query query = entityManager.createNativeQuery(SQL, ForumLetter.class).setParameter(1, memberId);
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        @SuppressWarnings("unchecked")
        final List<ForumLetter> result = query.getResultList();
        return new Page<ForumLetter>() {
            @Override
            public long getTotalElements() {
                return count;
            }
            
            @Override
            public Stream<ForumLetter> getResult() {
                return result.stream();
            }
        };
    }
    private long countByReceiverGroupSender(long memberId) {
        try {
            List<Object[]> rs = entityManager.createQuery("SELECT ib.sender, COUNT(ib) FROM Inbox ib WHERE ib.member = ?1 GROUP BY ib.sender")
                    .setParameter(1, memberId)
                    .getResultList();
            return Long.valueOf(rs.size());
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[countByReceiver][InboxDao]", e);
            }
        }
        return 0L;
    }
    /**
     * [NativeSQL]
     * @param memberId
     * @param typed
     * @param pageable
     * @return 
     */
    @Override
    public Page<ForumLetter> findAllByReceiverAndType(long memberId, ForumLetterTypeEnum typed, Pageable pageable) {
        final long count = countByReceiverAndType(memberId, typed);
        if (count == 0) {
            return emptyResult();
        }
        String SQL = "SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.USABLE = ?2 AND fl.TYPED = ?3 ORDER BY ib.ID DESC";
        Query query = entityManager.createNativeQuery(SQL, ForumLetter.class).setParameter(1, memberId).setParameter(2, 1).setParameter(3, typed.name());
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        
        @SuppressWarnings("unchecked")
        final List<ForumLetter> result = query.getResultList();
        return new Page<ForumLetter>() {
            @Override
            public long getTotalElements() {
                return count;
            }
            
            @Override
            public Stream<ForumLetter> getResult() {
                return result.stream();
            }
        };
    }
    /**
     * [NativeSQL]
     * @param memberId
     * @param typed
     * @return 
     */
    private long countByReceiverAndType(long memberId, ForumLetterTypeEnum typed) {
        try {
            String SQL = "SELECT COUNT(fl.id) FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.USABLE = ?2 AND fl.TYPED = ?3";
            Object obj = entityManager.createNativeQuery(SQL)
                    .setParameter(1, memberId)
                    .setParameter(2, 1)
                    .setParameter(3, typed.name())
                    .getSingleResult();
            try {
                return ((BigDecimal) obj).longValue();
            } catch (java.lang.ClassCastException e) {
                return (Long) obj;
            }
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[countByReceiverAndType][InboxDao]", e);
            }
        }
        return 0L;
    }
    /**
     * [NativeSQL]
     * @param sender
     * @param memberId
     * @return 
     */
    @Override
    @SuppressWarnings("unchecked")
    public Stream<ForumLetter> findAll(long sender, long memberId) {
        String SQL = "SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.USABLE = ?2 AND fl.AUTHOR = ?3 ORDER BY fl.ENTRYDATETIME ASC";
        return entityManager.createNativeQuery(SQL, ForumLetter.class).setParameter(1, memberId).setParameter(2, true).setParameter(3, sender).getResultStream();
    }
    /**
     * [NativeSQL]
     * @param sender
     * @param memberId
     * @param startDateTime
     * @return 
     */
    @Override
    public Stream<ForumLetter> findAll(long sender, long memberId, LocalDateTime startDateTime) {
        LocalDateTime finishDateTime = LocalDateTime.now();
        String SQL = "SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.USABLE = ?2 AND fl.AUTHOR = ?3 AND fl.ENTRYDATETIME BETWEEN ?4 AND ?5 ORDER BY fl.ENTRYDATETIME DESC";
        return entityManager.createNativeQuery(SQL, ForumLetter.class)
                .setParameter(1, memberId)
                .setParameter(2, true)
                .setParameter(3, sender)
                .setParameter(4, startDateTime)
                .setParameter(5, finishDateTime)
                .getResultStream();
    }
    /**
     * [NativeSQL]
     * @param memberId
     * @param size
     * @return 
     */
    @Override
    @SuppressWarnings("unchecked")
    public Stream<ForumLetter> findAllForUnReadable(long memberId, int size) {
        String SQL="SELECT fl.* FROM apo_letter AS fl JOIN apo_letter_inbox AS ib ON fl.ID = ib.LETTER WHERE ib.MEMBER = ?1 AND ib.READABLE = ?2 AND ib.USABLE = ?3";
        return entityManager.createNativeQuery(SQL, ForumLetter.class).setMaxResults(size).setParameter(1, memberId).setParameter(2, true).setParameter(3, true).getResultStream();
    }
    
    @Override
    public Stream<Inbox> findAllByLetter(Collection<Long> letterIdSet) {
        if (letterIdSet == null || letterIdSet.isEmpty()) {
            return Stream.empty();
        }
        return entityManager.createQuery("SELECT ib FROM Inbox ib WHERE ib.letter IN ?1", Inbox.class).setParameter(1, letterIdSet).getResultStream();
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public Optional<Boolean> editReadabled(long memberId, long sender) {
        int affect = entityManager.createQuery("UPDATE Inbox ib SET ib.readable = ?1 WHERE ib.member = ?2  AND ib.sender = ?3").setParameter(1, false).setParameter(2, memberId).setParameter(3, sender).executeUpdate();
        return affect >0?Optional.of(true):Optional.empty();
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public int editReadabled(long memberId, List<Long> letterIdList) {
        if (letterIdList == null || letterIdList.isEmpty()) {
            return 0;
        }
        int affect = entityManager.createQuery("UPDATE Inbox ib SET ib.readable = ?1 WHERE ib.member = ?2  AND ib.letter IN ?3").setParameter(1, false).setParameter(2, memberId).setParameter(3, letterIdList).executeUpdate();
        return affect;
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public int editReadabled(long memberId) {
        int affect = entityManager.createQuery("UPDATE Inbox ib SET ib.readable = ?1 WHERE ib.member = ?2  AND ib.readable = ?3").setParameter(1, false).setParameter(2, memberId).setParameter(3, true).executeUpdate();
        return affect;
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public int editDeleted(long memberId, List<Long> letterIdList) {
        if (letterIdList == null || letterIdList.isEmpty()) {
            return 0;
        }
        int affect = entityManager.createQuery("UPDATE Inbox ib SET ib.usable = ?1 WHERE ib.member = ?2  AND ib.letter IN ?3").setParameter(1, false).setParameter(2, memberId).setParameter(3, letterIdList).executeUpdate();
        return affect;
    }
    
    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public int batchSave(Set<Inbox> letterEntryRecords) {
        int i = 0;
        for (Inbox ib : letterEntryRecords) {
            entityManager.persist(ib);
            i++;
            if (i % batchSize == 0) {
                // Flush a batch of inserts and release memory.
                entityManager.flush();
                entityManager.clear();
            }
        }
        return i;
    }
    
    @Override
    public long countForUnReadable(long memberId) {
        try {
            return entityManager.createQuery("SELECT COUNT(ib) FROM Inbox ib WHERE ib.member = ?1 AND ib.readable = ?2 AND ib.usable = ?3", Long.class)
                    .setParameter(1, memberId)
                    .setParameter(2, true)
                    .setParameter(3, true)
                    .getSingleResult();
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[countForUnReadable][InboxDao]", e);
            }
        }
        return 0L;
    }
    
    @Override
    public Map<Long, Long> groupForUnReadable(long memberId, Set<Long> senderIdSet) {
        if (senderIdSet == null || senderIdSet.isEmpty()) {
            return Collections.emptyMap();
        }
        final String SQL = "SELECT ib.sender, COUNT(ib) FROM Inbox ib WHERE ib.member = ?1 AND ib.readable = ?2 AND ib.usable = ?3 AND ib.sender IN ?4 GROUP BY ib.sender";
        Map<Long, Long> result = new HashMap<>();
        try {
            List<Object[]> rs = entityManager.createQuery(SQL)
                    .setParameter(1, memberId)
                    .setParameter(2, true)
                    .setParameter(3, true)
                    .setParameter(4, senderIdSet)
                    .getResultList();
            for (Object[] arr : rs) {
                Long sender = (Long) arr[0];
                //
                Long count = 0L;
                try {
                    count = ((BigDecimal) arr[1]).longValue();
                } catch (java.lang.ClassCastException e) {
                    count = (Long) arr[1];
                }
                //
                if (sender != null && sender > 0) {
                    result.put(sender, count);
                }
            }
        } catch (Exception e) {
            if (logger.isDebugEnabled()) {
                logger.debug("[collectUnreadSize][InboxDao]", e);
            }
        }
        return result;
    }
    
    @Override
    public Page<ForumLetter> findAll(Pageable pageable) {
        return emptyResult();
    }
    
    @Override
    public void save(ForumLetter entity) {}
    
    @Override
    public Optional<ForumLetter> findOne(Long primaryKey) {
        return Optional.empty();
    }
    
    @Override
    public Optional<Boolean> edit(ForumLetter updateEntity) {
        return Optional.empty();
    }
    
    @Override
    public Stream<ForumLetter> findAll() {
        return Stream.empty();
    }
    
    @Override
    public long count() {
        return 0L;
    }
}